﻿using System;
using System.Data;
using System.Data.SqlClient;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public sealed class DirectorateDao : IDisposable
    {
        private readonly SqlConnection _sqlConnection;
        /// <summary>
        /// Initializes a new instance of the <see cref="DirectorateDao"/> class.
        /// </summary>
        public DirectorateDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="DirectorateDao"/> class.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        public DirectorateDao(string connectionString)
        {
            _sqlConnection = new SqlConnection(connectionString);
        }
        /// <summary>
        /// Lists all directorates.
        /// </summary>
        /// <returns></returns>
        public DataTable ListAllDirectorates(int organizationId)
        {
            try
            {
                var sqlParam = new SqlParameter[1];
                sqlParam[0] = new SqlParameter("@OrganizationId",SqlDbType.Int){Value = organizationId};
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, DBSchemas.ListDirectorates,sqlParam);
                return dataSet.Tables[0];
            }
            catch (SqlException exception) 
            {
                ExceptionHandler.WriteErrorLog(exception);
                return null;
            }
        }

        /// <summary>
        /// Creates the directorate.
        /// </summary>
        /// <param name="directorate">The directorate.</param>
        /// <returns></returns>
        public int CreateDirectorate(Directorate directorate)
        {
            try
            {
                var parameters = new SqlParameter[21];
                parameters[0] = new SqlParameter("@OrganizationId", SqlDbType.Int) { Value = directorate.OrganizationId };
                parameters[1] = new SqlParameter("@ContactId", SqlDbType.Int) { Value = directorate.ContactId };
                parameters[2] = new SqlParameter("@DirectorateName", SqlDbType.NVarChar) { Value = directorate.DirectorateName };
                parameters[3] = new SqlParameter("@ShortDescription", SqlDbType.NVarChar) { Value = directorate.DirectorateShortDescription};
                parameters[4] = new SqlParameter("@AddressLine1", SqlDbType.NVarChar) { Value = directorate.AddressLine1 };
                parameters[5] = new SqlParameter("@AddressLine2", SqlDbType.NVarChar) { Value = directorate.AddressLine2 };
                parameters[6] = new SqlParameter("@AddressLine3", SqlDbType.NVarChar) { Value = directorate.AddressLine3 };
                parameters[7] = new SqlParameter("@Postcode", SqlDbType.NVarChar) { Value = directorate.Postcode };
                parameters[8] = new SqlParameter("@Town", SqlDbType.NVarChar) { Value = directorate.Town };
                parameters[9] = new SqlParameter("@County", SqlDbType.NVarChar) { Value = directorate.County };
                parameters[10] = new SqlParameter("@Country", SqlDbType.NVarChar) { Value = directorate.Country };
                parameters[11] = new SqlParameter("@TypeOfBusinessId", SqlDbType.Int) { Value = directorate.TypeOfBusinessId };
                parameters[12] = new SqlParameter("@SiCcode", SqlDbType.NVarChar) { Value = directorate.SICcode };
                parameters[13] = new SqlParameter("@FullDescription", SqlDbType.NVarChar) { Value = directorate.DirectorateFullDescription};
                parameters[14] = new SqlParameter("@PhoneNumber", SqlDbType.NVarChar) { Value = directorate.PhoneNumber };
                parameters[15] = new SqlParameter("@Fax", SqlDbType.NVarChar) { Value = directorate.Fax };
                parameters[16] = new SqlParameter("@Email", SqlDbType.NVarChar) { Value = directorate.Email };
                parameters[17] = new SqlParameter("@WebAddress", SqlDbType.NVarChar) { Value = directorate.WebAddress };
                parameters[18] = new SqlParameter("@CharityNumber", SqlDbType.NVarChar) { Value = directorate.CharityNumber };
                parameters[19] = new SqlParameter("@CompanyNumber", SqlDbType.NVarChar) { Value = directorate.CompanyNumber };
                parameters[20] = new SqlParameter("@Status", SqlDbType.Bit) { Value = directorate.IsActive };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.CreateDirectorate, parameters);
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
        }

        /// <summary>
        /// Updates the directorate.
        /// </summary>
        /// <param name="directorate">The directorate.</param>
        /// <returns></returns>
        public int UpdateDirectorate(Directorate directorate)
        {
            try
            {
                var parameters = new SqlParameter[22];
                parameters[0] = new SqlParameter("@OrganizationId", SqlDbType.Int) { Value = directorate.OrganizationId };
                parameters[1] = new SqlParameter("@ContactId", SqlDbType.Int) { Value = directorate.ContactId };
                parameters[2] = new SqlParameter("@DirectorateName", SqlDbType.NVarChar) { Value = directorate.DirectorateName };
                parameters[3] = new SqlParameter("@ShortDescription", SqlDbType.NVarChar) { Value = directorate.DirectorateShortDescription };
                parameters[4] = new SqlParameter("@AddressLine1", SqlDbType.NVarChar) { Value = directorate.AddressLine1 };
                parameters[5] = new SqlParameter("@AddressLine2", SqlDbType.NVarChar) { Value = directorate.AddressLine2 };
                parameters[6] = new SqlParameter("@AddressLine3", SqlDbType.NVarChar) { Value = directorate.AddressLine3 };
                parameters[7] = new SqlParameter("@Postcode", SqlDbType.Int) { Value = directorate.Postcode };
                parameters[8] = new SqlParameter("@Town", SqlDbType.NVarChar) { Value = directorate.Town };
                parameters[9] = new SqlParameter("@County", SqlDbType.NVarChar) { Value = directorate.County };
                parameters[10] = new SqlParameter("@Country", SqlDbType.NVarChar) { Value = directorate.Country };
                parameters[11] = new SqlParameter("@TypeOfBusinessId", SqlDbType.Int) { Value = directorate.TypeOfBusinessId };
                parameters[12] = new SqlParameter("@SiCcode", SqlDbType.NVarChar) { Value = directorate.SICcode };
                parameters[13] = new SqlParameter("@FullDescription", SqlDbType.NVarChar) { Value = directorate.DirectorateFullDescription};
                parameters[14] = new SqlParameter("@PhoneNumber", SqlDbType.NVarChar) { Value = directorate.PhoneNumber };
                parameters[15] = new SqlParameter("@Fax", SqlDbType.NVarChar) { Value = directorate.Fax };
                parameters[16] = new SqlParameter("@Email", SqlDbType.NVarChar) { Value = directorate.Email };
                parameters[17] = new SqlParameter("@WebAddress", SqlDbType.NVarChar) { Value = directorate.WebAddress };
                parameters[18] = new SqlParameter("@CharityNumber", SqlDbType.NVarChar) { Value = directorate.CharityNumber };
                parameters[19] = new SqlParameter("@CompanyNumber", SqlDbType.NVarChar) { Value = directorate.CompanyNumber };
                parameters[20] = new SqlParameter("@Status", SqlDbType.Bit) { Value = directorate.IsActive };
                parameters[21] = new SqlParameter("@DirectorateId", SqlDbType.Int) { Value = directorate.DirectorateId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.AmendDirectorate, parameters);
            }
            catch (InvalidCastException ex)
            {
                ExceptionHandler.WriteErrorLog(ex);
                return 0;
            }
            catch(SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return 0;
            }
        }

        /// <summary>
        /// Gets the directorate by id.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns></returns>
        public Directorate GetDirectorateById(int id)
        {
            try
            {
                var parameter = new SqlParameter("@DirectorateId", SqlDbType.Int) { Value = id };
                var dataSet =
                    SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, DBSchemas.GetDirectorateById, parameter);
                var dataRow = dataSet.Tables[0].Rows[0];
                return new Directorate
                           {
                               DirectorateId = (int)dataRow["DirectorateId"],
                               OrganizationId = (int)dataRow["OrganizationId"],
                               ContactId = (int)dataRow["ContactId"],
                               DirectorateName = (string)dataRow["DirectorateName"],
                               DirectorateShortDescription = (string)dataRow["ShortDescription"],
                               AddressLine1 = (string)dataRow["AddressLine1"],
                               AddressLine2 = (string)dataRow["AddressLine2"],
                               AddressLine3 = (string)dataRow["AddressLine3"],
                               Postcode = (string)dataRow["Postcode"],
                               Town = (string)dataRow["Town"],
                               County = (string)dataRow["County"],
                               Country = (string)dataRow["Country"],
                               TypeOfBusinessId = (int)dataRow["TypeOfBusinessId"],
                               SICcode = (string)dataRow["SiCcode"],
                               DirectorateFullDescription = (string)dataRow["FullDescription"],
                               PhoneNumber = (string)dataRow["PhoneNumber"],
                               Fax = (string)dataRow["Fax"],
                               Email = (string)dataRow["Email"],
                               WebAddress = (string)dataRow["WebAddress"],
                               CharityNumber = (string)dataRow["CharityNumber"],
                               CompanyNumber = (string)dataRow["CompanyNumber"],
                               IsActive = (bool)dataRow["Status"]
                           };
            }
            catch (InvalidCastException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return null;
            }
            catch(SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);
                return null;
            }
        }

        /// <summary>
        /// Changes the directorate status.
        /// </summary>
        /// <param name="directorateId">The directorate id.</param>
        /// <returns></returns>
        public int ChangeDirectorateStatus(int directorateId)
        {
            try
            {
                var parameter = new SqlParameter("@DirectorateId", SqlDbType.Int) { Value = directorateId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.ActivateDirectorate, parameter);
            }
            catch (SqlException sqlException)
            {
                ExceptionHandler.WriteErrorLog(sqlException);
                return 0;
            }
            
        }
        /// <summary>
        /// Makes the directorate is active.
        /// </summary>
        /// <param name="directorateId">The directorate id.</param>
        /// <returns></returns>
        public int MakeDirectorateIsActive(int directorateId)
        {
            try
            {
                var parameter = new SqlParameter("@DirectorateId", SqlDbType.Int) {Value = directorateId};
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, DBSchemas.MakeDirectorateIsActive,
                                                 parameter);
            }
            catch (SqlException exception)
            {
                ExceptionHandler.WriteErrorLog(exception);   
                return 0;
            }
        }
        /// <summary>
        /// Checks the directorate name is exist.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public int CheckDirectorateNameIsExist(string name)
        {
            try
            {
                var paramneter = new SqlParameter("@DirectorateName", SqlDbType.NVarChar) {Value = name};
                return
                    (int)
                    SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, DBSchemas.CheckDirectorateNameIsExist,
                                            paramneter);
            }
            catch (SqlException ex)
            {
                ExceptionHandler.WriteErrorLog(ex);
                return 0;
            }
        }
        #region Dispose
        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                _sqlConnection.Close();
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion
    }
}
